<!DOCTYPE html>
<html lang="zh">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="baidu-site-verification" content="SCzNOI2Wq6" />
    <meta name="renderer" content="webkit">
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no">
    <title>
        daozhuang
    </title>
    
<link rel="stylesheet" href="/libs/highlight/styles/monokai-sublime.css">

    
<link rel="stylesheet" href="/libs/font-awesome/css/font-awesome.min.css">

    
<link rel="stylesheet" href="/css/style.css">

<meta name="generator" content="Hexo 4.2.1"></head>

<body id="bodyx">
    <div class="hd posts">
    <a href="/index.html"><i class="fa fa-reply replay-btn" aria-hidden="true"></i></a>
    <div class="post-title">
        <p>
            springboot 整合 druid 数据库连接池
        </p>
        <hr>
    </div>
    <div class="post-content">
        <h1 id="springboot-整合-druid-数据库连接池"><a href="#springboot-整合-druid-数据库连接池" class="headerlink" title="springboot 整合 druid 数据库连接池"></a>springboot 整合 druid 数据库连接池</h1><h5 id="在-pom-文件中添加依赖"><a href="#在-pom-文件中添加依赖" class="headerlink" title="在 pom 文件中添加依赖"></a>在 pom 文件中添加依赖</h5><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">&lt;!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter --&gt;</span></span><br><span class="line"><span class="tag">&lt;<span class="name">dependency</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">groupId</span>&gt;</span>com.alibaba<span class="tag">&lt;/<span class="name">groupId</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">artifactId</span>&gt;</span>druid-spring-boot-starter<span class="tag">&lt;/<span class="name">artifactId</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">version</span>&gt;</span>1.1.23<span class="tag">&lt;/<span class="name">version</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;/<span class="name">dependency</span>&gt;</span></span><br></pre></td></tr></table></figure>

<h4 id="在application-yaml文件中配置"><a href="#在application-yaml文件中配置" class="headerlink" title="在application.yaml文件中配置"></a>在application.yaml文件中配置</h4><p>以下配置文件中，除了基本的配置以外还配置了：</p>
<p>防止sql注入、sql监控、启用内置监控页面、开启慢SQL统计</p>
<p>内置监控页面开启之后的地址为：项目地址后面加 druid</p>
<figure class="highlight yaml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br></pre></td><td class="code"><pre><span class="line"><span class="attr">spring:</span></span><br><span class="line">  <span class="attr">datasource:</span></span><br><span class="line">    <span class="attr">name:</span> <span class="string">druidDataSource</span></span><br><span class="line">    <span class="attr">type:</span> <span class="string">com.alibaba.druid.pool.DruidDataSource</span></span><br><span class="line">    <span class="attr">druid:</span></span><br><span class="line">      <span class="attr">url:</span> <span class="string">jdbc:mysql://127.0.0.1:3306/edu</span></span><br><span class="line">      <span class="attr">username:</span> <span class="string">root</span></span><br><span class="line">      <span class="attr">password:</span> <span class="number">123456</span></span><br><span class="line">      <span class="attr">driver-class-name:</span> <span class="string">com.mysql.jdbc.Driver</span></span><br><span class="line">      <span class="attr">initial-size:</span> <span class="number">1</span>																					<span class="comment"># 初始化链接数</span></span><br><span class="line">      <span class="attr">min-idle:</span> <span class="number">1</span>																							<span class="comment"># 最小保留连接数</span></span><br><span class="line">      <span class="attr">max-active:</span> <span class="number">30</span>																					<span class="comment"># 最大连接数</span></span><br><span class="line">      <span class="attr">max-wait:</span> <span class="number">60000</span>																					<span class="comment"># 超时时间</span></span><br><span class="line">      <span class="attr">time-between-eviction-runs-millis:</span> <span class="number">60000</span>			<span class="comment">#空闲连接检查、废弃连接清理、空闲连接池大小调整之间的操作时间间隔</span></span><br><span class="line">      <span class="attr">min-evictable-idle-time-millis:</span> <span class="number">300000</span>				<span class="comment"># 指定一个空闲连接最少空闲多久后可被清除</span></span><br><span class="line">      <span class="attr">validation-query:</span> <span class="string">SELECT</span> <span class="number">1</span>										<span class="comment"># 链接断开检查语句</span></span><br><span class="line">      <span class="attr">test-while-idle:</span> <span class="literal">true</span>													<span class="comment"># 超时检测</span></span><br><span class="line">      <span class="attr">test-on-borrow:</span> <span class="literal">false</span>													<span class="comment"># 即在获取Connection对象时检测其可用性</span></span><br><span class="line">      <span class="string">test-on-return=:</span> <span class="literal">false</span>												<span class="comment"># 设置当连接被归还时，是否要提交所有还未完成的事务</span></span><br><span class="line">      <span class="attr">pool-prepared-statements:</span> <span class="literal">true</span>								<span class="comment"># 打开PSCache，并且指定每个连接上PSCache的大小</span></span><br><span class="line">      <span class="attr">max-pool-prepared-statement-per-connection-size:</span> <span class="number">20</span></span><br><span class="line">      <span class="comment"># 过滤器设置（第一个stat很重要，没有的话会监控不到SQL）</span></span><br><span class="line">      <span class="attr">filters:</span> <span class="string">stat,wall,log4j2</span></span><br><span class="line">      <span class="comment"># 'wall'用于防火墙 防止sql注入</span></span><br><span class="line">      <span class="comment"># 启用StatFilter</span></span><br><span class="line">      <span class="attr">web-stat-filter:</span></span><br><span class="line">        <span class="attr">enabled:</span> <span class="literal">true</span></span><br><span class="line">        <span class="attr">url-pattern:</span> <span class="string">/*</span>                 <span class="comment"># 添加过滤规则</span></span><br><span class="line">        <span class="attr">exclusions:</span> <span class="string">/druid/*</span> <span class="comment"># 排除一些不必要的url</span></span><br><span class="line">        <span class="attr">session-stat-enable:</span> <span class="literal">true</span>       <span class="comment"># 开启session统计功能</span></span><br><span class="line">        <span class="attr">session-stat-max-count:</span> <span class="number">1000</span>    <span class="comment"># 缺省sessionStatMaxCount是1000个</span></span><br><span class="line">      <span class="comment">#spring.datasource.druid.web-stat-filter.principal-session-name=</span></span><br><span class="line">      <span class="comment">#spring.datasource.druid.web-stat-filter.principal-cookie-name=</span></span><br><span class="line">      <span class="comment">#spring.datasource.druid.web-stat-filter.profile-enable=</span></span><br><span class="line">      <span class="comment">##### StatViewServlet配置 #######</span></span><br><span class="line">      <span class="comment">#启用内置的监控页面</span></span><br><span class="line">      <span class="attr">stat-view-servlet:</span></span><br><span class="line">        <span class="attr">enabled:</span> <span class="literal">true</span></span><br><span class="line">        <span class="attr">url-pattern:</span> <span class="string">/druid/*</span>           <span class="comment">#内置监控页面的地址</span></span><br><span class="line">        <span class="attr">reset-enable:</span> <span class="literal">false</span>             <span class="comment">#关闭 Reset All 功能</span></span><br><span class="line">        <span class="attr">login-username:</span> <span class="string">admin</span>           <span class="comment">#设置登录用户名</span></span><br><span class="line">        <span class="attr">login-password:</span> <span class="number">123</span>             <span class="comment">#设置登录密码</span></span><br><span class="line">        <span class="attr">allow:</span> <span class="number">127.0</span><span class="number">.0</span><span class="number">.1</span>                <span class="comment">#白名单（如果allow没有配置或者为空，则允许所有访问）</span></span><br><span class="line">        <span class="attr">deny:</span>                           <span class="comment">#黑名单（deny优先于allow，如果在deny列表中，就算在allow列表中，也会被拒绝）</span></span><br><span class="line">      <span class="comment"># 开启慢SQL统计（这里超过500毫秒则判定为慢SQL）</span></span><br><span class="line">      <span class="attr">filter:</span></span><br><span class="line">        <span class="attr">stat:</span></span><br><span class="line">          <span class="attr">enabled:</span> <span class="literal">true</span></span><br><span class="line">          <span class="attr">log-slow-sql:</span> <span class="literal">true</span></span><br><span class="line">          <span class="attr">slow-sql-millis:</span> <span class="number">500</span></span><br></pre></td></tr></table></figure>

<p>以上的参数都是作者根据自身编写的，最终的优化还是要根据自己的实际情况配置。</p>
<p><strong>druid 中使用ConfigFilter对配置文件进行操作</strong></p>
<p><strong>ConfigFilter的作用：</strong></p>
<ul>
<li>从配置文件中读取配置</li>
<li>从远程http文件中读取配置</li>
<li>为数据库密码提供加密功能</li>
</ul>
<h4 id="数据库密码加密"><a href="#数据库密码加密" class="headerlink" title="数据库密码加密"></a>数据库密码加密</h4><p>在命令行中执行如下命令：</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">java -cp druid-1.0.16.jar com.alibaba.druid.filter.config.ConfigTools you_password</span><br></pre></td></tr></table></figure>

<p>输出</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">privateKey:MIIBVgIBADANBgkqhkiG9w0BAQEFAASCAUAwggE8AgEAAkEA6+4avFnQKP+O7bu5YnxWoOZjv3no4aFV558HTPDoXs6EGD0HP7RzzhGPOKmpLQ1BbA5viSht+aDdaxXp6SvtMQIDAQABAkAeQt4fBo4SlCTrDUcMANLDtIlax&#x2F;I87oqsONOg5M2JS0jNSbZuAXDv7&#x2F;YEGEtMKuIESBZh7pvVG8FV531&#x2F;fyOZAiEA+POkE+QwVbUfGyeugR6IGvnt4yeOwkC3bUoATScsN98CIQDynBXC8YngDNwZ62QPX+ONpqCel6g8NO9VKC+ETaS87wIhAKRouxZL38PqfqV&#x2F;WlZ5ZGd0YS9gA360IK8zbOmHEkO&#x2F;AiEAsES3iuvzQNYXFL3x9Tm2GzT1fkSx9wx+12BbJcVD7AECIQCD3Tv9S+AgRhQoNcuaSDNluVrL&#x2F;B&#x2F;wOmJRLqaOVJLQGg&#x3D;&#x3D;</span><br><span class="line">publicKey:MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAOvuGrxZ0Cj&#x2F;ju27uWJ8VqDmY7956OGhVeefB0zw6F7OhBg9Bz+0c84RjzipqS0NQWwOb4kobfmg3WsV6ekr7TECAwEAAQ&#x3D;&#x3D;</span><br><span class="line">password:PNak4Yui0+2Ft6JSoKBsgNPl+A033rdLhFw+L0np1o+HDRrCo9VkCuiiXviEMYwUgpHZUFxb2FpE0YmSguuRww&#x3D;&#x3D;</span><br></pre></td></tr></table></figure>

<p>在 application.properties 中配置以下选项</p>
<figure class="highlight properties"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">spring.datasource.url</span>=<span class="string">jdbc:mysql://localhost:3306/test</span></span><br><span class="line"><span class="meta">spring.datasource.username</span>=<span class="string">root</span></span><br><span class="line"><span class="comment"># 生成的加密后的密码（原密码 123456）</span></span><br><span class="line"><span class="meta">spring.datasource.password</span>=<span class="string">WVMjPhfXQrIsWRo0/RCqAVvYtTU9WNVToKJohb8AlUmHwnV6vwFL+FM2CNFDMJwGHW1iCmyaUlF+sgvFdogqEA==</span></span><br><span class="line"><span class="comment"># 生成的公钥</span></span><br><span class="line"><span class="meta">public-key</span>=<span class="string">MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAIiwHpFrDijV+GzwRTzWJk8D3j3jFfhsMFJ/7k1NTvBuLgL+TdIHgaMNOIEjHpXzuvX38J3FtOK8hLrySncVGOMCAwEAAQ==</span></span><br><span class="line"><span class="comment"># 配置 connection-properties，启用加密，配置公钥。</span></span><br><span class="line"><span class="meta">spring.datasource.druid.connection-properties</span>=<span class="string">config.decrypt=true;config.decrypt.key=$&#123;public-key&#125;</span></span><br><span class="line"><span class="comment"># 启用ConfigFilter</span></span><br><span class="line"><span class="meta">spring.datasource.druid.filter.config.enabled</span>=<span class="string">true</span></span><br></pre></td></tr></table></figure>


    </div>

    
        <hr class="fhr">
        <div id="vcomments"></div>
    
</div>
    <div class="footer" id="footer">
    <p>Copyright © 2020 <a class="flink" href="https://hexo.io" target="_blank" rel="noopener">Hexo</a>-<a class="flink" href="https://github.com/sanjinhub/hexo-theme-geek" target="_blank" rel="noopener">Geek</a>.
        <label class="el-switch el-switch-green el-switch-sm" style="vertical-align: sub;">
            <input type="checkbox" name="switch" id="update_style">
            <span class="el-switch-style"></span>
        </label>
<!--         <script type="text/javascript">
        var cnzz_protocol = (("https:" == document.location.protocol) ? "https://" : "http://");
        document.write(unescape("%3Cspan id='cnzz_stat_icon_1278548644'%3E%3C/span%3E%3Cscript src='" + cnzz_protocol + "v1.cnzz.com/stat.php%3Fid%3D1278548644%26show%3Dpic1' type='text/javascript'%3E%3C/script%3E"));
        </script> -->
    </p>
</div>
<input type="hidden" id="web_style" value="black">
<input type="hidden" id="valine_appid" value="c994h5wXLa8fSMa424BTyVhs-gzGzoHsz">
<input type="hidden" id="valine_appKey" value="4tFbwCi4sYo3yRFgiNFpQd4N">

<script src="/libs/jquery.min.js"></script>


<script src="/libs/highlight/highlight.pack.js"></script>

<script src='//cdn.jsdelivr.net/npm/valine@1.3.10/dist/Valine.min.js'></script>

<script src="/js/js.js"></script>

<style type="text/css">
.v * {
    color: #698fca;
}

.v .vlist .vcard .vhead .vsys {
    color: #3a3e4a;
}

.v .vlist .vcard .vh .vmeta .vat {
    color: #638fd5;
}

.v .vlist .vcard .vhead .vnick {
    color: #6ba1ff;
}

.v a {
    color: #8696b1;
}

.v .vlist .vcard .vhead .vnick:hover {
    color: #669bfc;
}
</style>
</body>

</html>